-- @owner: lvlintao666
-- @date: 2024/05/13
-- @testpoint: 在connect by中使用prior递归查询
--step1:创建间隔分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_prior_0001;
create table t_prior_0001
(pid int,
id int,
name varchar(100),
"int1" tinyint,
"int2" smallint,
"int4" integer,
"int8" bigint,
"float4" float4,
"float8" float8,
"numeric" decimal(20, 6),
"bit4" bit(4),
"boolean" boolean,
"date" date,
"time" time,
"timetz" timetz,
"timestamp" timestamp,
"timestamptz" timestamptz,
"char" char(100),
"varchar" varchar(100),
"blob" blob,
"text" text
);

insert into t_prior_0001 values (0,1,'node1',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0001 values (1,2,'node2',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0001 values (1,3,'node3',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0001 values (2,4,'node4',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0001 values (2,5,'node5',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0001 values (3,6,'node6',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0001 values (3,7,'node7',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');

--step2 在connect by中使用prior查询出正确结果;expect:成功
select prior name as father_name, prior "int1" as father_int1, name as current_name, "int1" as current_int1 from t_prior_0001 connect by pid = prior id;

--step3 清理环境;expect:成功
drop table if exists t_prior_0001 cascade;